package patientimport;

import java.io.BufferedReader;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.bbcare.comm.AppException;
import com.bbcare.comm.ErrorCode;
import com.bbcare.comm.ToolUtils;

public class Shujuyuan {
 	/*public static final String POST_URL_CALL = "http://doctor.bb-care.com:9090/followup/service/patient/patientAdd?sessionId=26bf589d-3274-4e4f-9f1c-22cf6c571506";
    public static final String POST_QUES_CALL = "http://doctor.bb-care.com:9090/followup/service/patient/patientTaskContentDataModify?sessionId=26bf589d-3274-4e4f-9f1c-22cf6c571506";
    public static final String dbUrl = "jdbc:mysql://rdsap8226j2iuruyoaamgpublic.mysql.rds.aliyuncs.com/bbcare_cloud";
    public static final String dbUrlOms = "jdbc:mysql://rdsap8226j2iuruyoaamgpublic.mysql.rds.aliyuncs.com/bbcare_oms";
    public static final String dbUser = "******";
    public static final String dbPwd = "******";*/

	public static final String POST_URL_CALL = "http://doctor.bb-care.com:9090/followup/service/patient/patientAdd?sessionId=0b389849-8015-4f5e-a0e9-0d00d06d3403";
    public static final String POST_QUES_CALL = "http://192.168.1.102:18080/followup/service/patient/patientTaskContentDataModify?sessionId=9c1101b9-11ce-4dc0-95f8-542050b9eb2d";
    //public static final String dbUrl = "jdbc:mysql://rdsap8226j2iuruyoaamgpublic.mysql.rds.aliyuncs.com/test_temp_user";
    //public static final String dbUrl = "jdbc:mysql://rdsap8226j2iuruyoaamgpublic.mysql.rds.aliyuncs.com/bbcare_cloud";
    public static final String dbUrlOms = "jdbc:mysql://rdsap8226j2iuruyoaamgpublic.mysql.rds.aliyuncs.com/test_test";
    //public static final String dbUrlOms = "jdbc:mysql://rdsap8226j2iuruyoaamgpublic.mysql.rds.aliyuncs.com/bbcare_oms";
    public static final String dbUser = "test_test_acc";
    public static final String dbPwd = "testAcc#2018";
    //public static final String dbUser = "yunyin";
    //public static final String dbPwd = "cl123456";

    //随访2.0生长发育接口
    public static final String POST_URL_GROETH = "http://192.168.1.105:8686/followup/webapi/service/wechat/growth/addgrowth";
    //智力发育和养育环境评估接口
    public static final String POST_URL_GETSCORE = "http://192.168.1.105:8686/followup/webapi/service/evaluation/getScore";

    public static JSONObject httpPostCall(String parm2, String callUrl) throws Exception {
        try {
            URL url = new URL(callUrl);
            //System.out.println("url==" + callUrl);

            // 将url 以 open方法返回的urlConnection 连接强转为HttpURLConnection连接
            // (标识一个url所引用的远程对象连接)
            HttpURLConnection connection = (HttpURLConnection) url.openConnection();// 此时cnnection只是为一个连接对象,待连接中

            // 设置连接输出流为true,默认false (post 请求是以流的方式隐式的传递参数)
            connection.setDoOutput(true);

            // 设置连接输入流为true
            connection.setDoInput(true);

            // 设置请求方式为post
            connection.setRequestMethod("POST");

            // post请求缓存设为false
            connection.setUseCaches(true);

            // 设置该HttpURLConnection实例是否自动执行重定向
            connection.setInstanceFollowRedirects(true);

            // 设置请求头里面的各个属性 (以下为设置内容的类型,设置为经过urlEncoded编码过的from参数)
            // application/x-javascript text/xml->xml数据
            // application/x-javascript->json对象
            // application/x-www-form-urlencoded->表单数据
            // ;charset=utf-8 必须要，不然会出现乱码
            connection.setRequestProperty("Content-Type", "application/json;charset=UTF-8");
            connection.setRequestProperty("Accept-Charset", "UTF-8");
            connection.setRequestProperty("contentType", "UTF-8");
            // connection.setRequestProperty("Cookie", cookie);

            // 建立连接
            // (请求未开始,直到connection.getInputStream()方法调用时才发起,以上各个参数设置需在此方法之前进行)
            connection.connect();

            // 创建输入输出流,用于往连接里面输出携带的参数,(输出内容为?后面的内容)
            OutputStreamWriter dataout = new OutputStreamWriter(connection.getOutputStream());
            //System.out.println("parm2==[" + parm2 + "]");
           // net.sf.json.JSONObject json = net.sf.json.JSONObject.fromObject(parm2);

            // 将参数输出到连接
            // dataout.writeBytes(parm2);
            dataout.write(parm2);

            // 输出完成后刷新并关闭流
            dataout.flush();
            dataout.close(); // 重要且易忽略步骤 (关闭流,切记!)

            // 连接发起请求,处理服务器响应 (从连接获取到输入流并包装为bufferedReader)
            BufferedReader bf = new BufferedReader(new InputStreamReader(connection.getInputStream(), "UTF-8"));
            String line;
            StringBuilder sb = new StringBuilder(); // 用来存储响应数据

            // 循环读取流,若不到结尾处
            while ((line = bf.readLine()) != null) {
                // sb.append(bf.readLine());
                sb.append(line).append(System.getProperty("line.separator"));
            }
            // ---{"success":true,"data":{"patientId":"14o4ajITLRMI5Zxc4ynK","ptSchemeId":"15pQ0sXjE67NEXSCxBIi"}}
            //System.out.println("---" + sb.toString());
            bf.close(); // 重要且易忽略步骤 (关闭流,切记!)
            connection.disconnect(); // 销毁连接

            JSONObject jsonArg = null;
            try {
                jsonArg = JSON.parseObject(sb.toString());
            } catch (Exception ex) {
                // ex.printStackTrace();
                throw (new AppException(ErrorCode.FOLLOWUP_EXCEPTION_TYPE_INPUT_DATA_FORMAT_ERROR, "出参格式错误，非json格式！"));
            }
            boolean retFlag = jsonArg.getBooleanValue("success");
            if (!retFlag) {
                throw (new AppException(ErrorCode.FOLLOWUP_EXCEPTION_TYPE_INPUT_DATA_FORMAT_ERROR,
                        "调用失败：" + jsonArg.getString("msg")));
            }
            if (POST_URL_CALL.equals(callUrl)) {
                JSONObject jsonData = jsonArg.getJSONObject("data");
                if (null == jsonData) {
                    throw (new AppException(ErrorCode.FOLLOWUP_EXCEPTION_TYPE_INPUT_DATA_FORMAT_ERROR,
                            "调用失败：返回无data节点!"));
                }
            }

            return jsonArg;
        } catch (Exception e) {
            e.printStackTrace();
            throw (e);
        }
	}

    public static void migrateData() {
        int irow = 0;
        int succNum = 0;
        int errNum = 0;
        // 声明Connection对象
        Connection con = null;
        Connection connOms = null;
        // 驱动程序名
        String driver = "com.mysql.jdbc.Driver";
        // URL指向要访问的数据库名mydata
        //String url = dbUrl;
        // MySQL配置时的用户名
        String user = dbUser;
        // MySQL配置时的密码
        String password = dbPwd;
        //队列和code和dept_id相关联
        Map<String,Object> mapQueue = new HashMap<String,Object>();


        try {
            // 加载驱动程序
            Class.forName(driver);
            // 1.getConnection()方法，连接MySQL数据库
           /* con = DriverManager.getConnection(url, user, password);
            if (!con.isClosed()) {
                //System.out.println("------Succeeded connecting to the Database!");
            }
            con.setAutoCommit(false);*/

            connOms = DriverManager.getConnection(dbUrlOms, user, password);
            if (!connOms.isClosed()) {
                //System.out.println("------Succeeded connecting to the oms Database!");
            }
            connOms.setAutoCommit(false);
        } catch (SQLException e) {
            // 数据库连接失败异常处理
            e.printStackTrace();
            return;
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return;
        }

        try {
        	//创建HSSFWorkbook对象
    		HSSFWorkbook wb = new HSSFWorkbook();
    		//创建HSSFSheet对象
    		HSSFSheet sheet = wb.createSheet("sheet0");
    		//创建HSSFRow对象
    		HSSFRow row = sheet.createRow(0);
    		//创建HSSFCell对象
    		HSSFCell cell=row.createCell(0);
    		//设置单元格的值
    		cell.setCellValue("label");
    		cell = row.createCell(1);
    		cell.setCellValue("innDeCode");
    		cell = row.createCell(2);
    		cell.setCellValue("deCode");
    		cell = row.createCell(3);
    		cell.setCellValue("tempId");
    		cell = row.createCell(4);
    		cell.setCellValue("contentId");
    		cell = row.createCell(5);
    		cell.setCellValue("contentName");
    		cell = row.createCell(6);
    		cell.setCellValue("taskName");
    		cell = row.createCell(7);
    		cell.setCellValue("taskId");
    		cell = row.createCell(8);
    		cell.setCellValue("eventCode");
    		cell = row.createCell(9);

            // 创建statement类对象，用来执行SQL语句！！
            Statement stmtOms = connOms.createStatement();
            Statement stmtOms1 = connOms.createStatement();
            String idsql = "select * from fu_contenttemplversion_dict where id like 'news%'";
            ResultSet idrs = stmtOms.executeQuery(idsql);
            int out= 0;
            int out1= 0;
            while (idrs.next()) {
            	String dte = idrs.getString("templ_data");
            	String tempId = idrs.getString("templ_id");
            	String contentId = idrs.getString("id");
            	String contentName = idrs.getString("remark");
            	String ids = "select GROUP_CONCAT(b.task_name) as taskName ,GROUP_CONCAT(b.id) AS taskId,b.event_code as eventCode from cp_nodetaskcont_info a "
            			+ ",cp_nodetask_info b where a.task_id=b.id  and a.templ_id ='"+tempId+"'GROUP BY b.id";
            	ResultSet idrs1 = stmtOms1.executeQuery(ids);
            	String taskName ="";
            	String taskId ="";
            	String eventCode ="";
            	while (idrs1.next()) {
            		taskName = idrs1.getString("taskName");
            		taskId = idrs1.getString("taskId");
            		eventCode = idrs1.getString("eventCode");
            	}
            	Map<String,Object> mapOut = tranferCpContentToMap(JSONObject.parse(dte));
            	if(MapUtils.isNotEmpty(mapOut)) {
	            	for (String in : mapOut.keySet()) {
	            			row = sheet.createRow((int) out + 1);
	    		            //map.keySet()返回的是所有key的值
	    		            JSONObject json = (JSONObject) mapOut.get(in);//得到每个key多对用value的值
	    		            if(null != json) {
	    		            	row = sheet.createRow((int) out + 1);
	    		            	if(null == json.get("label")) {
	    		            		row.createCell(0).setCellValue("");
	    		            	}else{
	    		            		row.createCell(0).setCellValue(json.get("label").toString());
	    		            	}
	    		            	if(StringUtils.isBlank((String)json.get("innDeCode"))) {
	    		            		row.createCell(1).setCellValue("");
	    		            	}else{
	    		            		row.createCell(1).setCellValue(json.get("innDeCode").toString());
	    		            	}
	    		            	if(StringUtils.isBlank((String)json.get("deCode"))) {
	    		            		row.createCell(2).setCellValue("");
	    		            	}else{
	    		            		row.createCell(2).setCellValue(json.get("deCode").toString());
	    		            	}
	    		            	row.createCell(3).setCellValue(tempId);
	    		            	row.createCell(4).setCellValue(contentId);
	    		            	row.createCell(5).setCellValue(contentName);
	    		            	row.createCell(6).setCellValue(taskName);
	    		            	row.createCell(7).setCellValue(taskId);
	    		            	row.createCell(8).setCellValue(eventCode);

	    		            }
	    		            out ++;
	            	 }

            	}
            	else {
            		System.out.println(mapOut);
            	}
            	out1++;
            }
            System.out.println(out1);
            try
    		{
    			FileOutputStream fout = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\innercode.xls");
    			wb.write(fout);
    			fout.close();
    			wb.close();
    		}
    		catch (Exception e)
    		{
    			e.printStackTrace();
    		}

            stmtOms.close();
            stmtOms1.close();
            //con.close();
            connOms.close();
            //stmtOms1.close();
        } catch (SQLException e) {
            // 数据库连接失败异常处理
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        } finally {
            //System.out.println("----------total: " + irow);
            //System.out.println("----------success: " + succNum);
            //System.out.println("----------error: " + errNum);
        }
    }

    /**将问卷id转换成key：value格式 ,value包含题目的所有配置*/
    public static  Map<String,Object> tranferCpContentToMap(Object content) {
    	if(null == content){
    		return null;
    	}
  		Map<String,Object> contentMap = new HashMap<String,Object>();
//  	for(int j =0,m=contentList.size();j<m;j++){
		if(content instanceof com.alibaba.fastjson.JSONArray){
			com.alibaba.fastjson.JSONArray arr = (com.alibaba.fastjson.JSONArray)content;
			for(int k =0;k<arr.size();k++){
				com.alibaba.fastjson.JSONArray chirldArr = arr.getJSONObject(k).getJSONArray("children");
				if(null != chirldArr){
					chirldJsonCpToMap(contentMap, chirldArr);
				}else{
					questionJsonCpToMap(contentMap,arr.getJSONObject(k));
				}
			}
		}else if(content instanceof JSONObject){
			com.alibaba.fastjson.JSONArray chirldArr = ((com.alibaba.fastjson.JSONObject)content).getJSONArray("children");
			if(null != chirldArr){
				chirldJsonCpToMap(contentMap, chirldArr);
			}else{
				questionJsonCpToMap(contentMap,(com.alibaba.fastjson.JSONObject)content);
			}

		}
//  	}
  		return contentMap;
  	}

    /**解析一个{}中 ，只是一个题目的情况*/
    public static void questionJsonCpToMap(Map<String, Object> contentMap,JSONObject json) {
  		if(null == json){
  			return;
  		}

  		if(!StringUtils.isEmpty(json.getString("deCode"))){
  			contentMap.put(json.getString("deCode"), json);
  		}
  	}

  	public static void chirldJsonCpToMap(Map<String, Object> contentMap,
  			com.alibaba.fastjson.JSONArray chirldArr) {
  		for(int l = 0 ;l<chirldArr.size();l++){
  			Object deCode  = null;
  			try {
  				deCode = chirldArr.getJSONObject(l).getObject("deCode",Object.class);
			} catch (Exception e) {
				com.alibaba.fastjson.JSONArray jspnm = chirldArr.getJSONArray(l);
				com.alibaba.fastjson.JSONObject josnd = jspnm.getJSONObject(0);
				chirldJsonCpToMap(contentMap,josnd.getJSONArray("children"));
			}
  			if(!org.springframework.util.StringUtils.isEmpty(deCode)){
  				if(deCode instanceof String){
  					contentMap.put(deCode.toString(),chirldArr.getJSONObject(l));
  				}else if(deCode instanceof com.alibaba.fastjson.JSONArray){
  					com.alibaba.fastjson.JSONArray idArr = (com.alibaba.fastjson.JSONArray)deCode;
  					for(int n = 0 ;n< idArr.size() ; n++){
  						contentMap.put(idArr.getString(n),chirldArr.getJSONObject(l));
  					}
  				}
  			}else{
  				String relationId = "";
  				try {
  	  				deCode = chirldArr.getJSONObject(l).getObject("deCode",Object.class);
  				} catch (Exception e) {
  					System.out.println(chirldArr.get(l));
  					com.alibaba.fastjson.JSONArray jspnm = chirldArr.getJSONArray(l);
  					com.alibaba.fastjson.JSONObject josnd = jspnm.getJSONObject(0);
  					chirldJsonCpToMap(contentMap,josnd.getJSONArray("children"));
  				}
  				if(!StringUtils.isEmpty(relationId)){
  					com.alibaba.fastjson.JSONArray jsonArr = chirldArr.getJSONObject(l).getJSONArray("children");
  					if(null != jsonArr){
  						for(int n = 0;n<jsonArr.size();n++){
  							String chirldDeCode = jsonArr.getJSONObject(n).getString("deCode");
  							if(!StringUtils.isEmpty(chirldDeCode)){
  								contentMap.put(chirldDeCode,jsonArr.getJSONObject(n));
  							}
  						}
  					}
  				}
  			}
  		}
  	}

    public static void main(String[] args) {
        try {
        	long time = System.currentTimeMillis();
            migrateData();
            long time2 = System.currentTimeMillis();
            //System.out.println("currentTimeMillis："+(time2-time));
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}



